package com.edu.oracle.dao;

import com.edu.oracle.pojo.Score;
import com.edu.oracle.pojo.vo.ScoreVo;
import com.edu.oracle.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class ScoreDao {//score表
    //全局变量类
    Connection connection=null;
    PreparedStatement preparedStatement = null;
    ResultSet resultSet=null;

    public List<ScoreVo> list() {//实现方法，实现功能
        //jdbc
        try {
            connection=JdbcUtils.getConnection();

            //3.操作对象
            String sql="  select student.sno,student.sname,student.ssex,student.speciality,course.cno, course.cname, score.grade " +
                    " from student,score,course where student.sno=score.sno and score.cno=course.cno";//sql里面不允许有分号
            preparedStatement= connection.prepareStatement(sql);
            resultSet=preparedStatement.executeQuery();//查询到的结果赋值给结果集对象

            //4.处理结果集
            //集合
            List<ScoreVo> scoreVos = new ArrayList<>();
            while(resultSet.next()){
                ScoreVo scoreVo = new ScoreVo();

                //student.sno,student.sname,student.ssex,student.speciality,course.cno, course.cname, score.grade
                scoreVo.setSno(resultSet.getString("sno"));

                scoreVo.setSname(resultSet.getString("sname"));
                scoreVo.setSsex(resultSet.getString("ssex"));
                scoreVo.setSpeciality(resultSet.getString("speciality"));
                scoreVo.setCno(resultSet.getString("cno"));
                scoreVo.setCname(resultSet.getString("cname"));
                scoreVo.setGrade(resultSet.getFloat("grade"));

                //集合存对象
                scoreVos.add(scoreVo);
            }
            return scoreVos;//查询成功，返回集合

        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            //关闭资源
            JdbcUtils.closeResoure(connection,preparedStatement,resultSet);
        }

        return null;
    }

    public ScoreVo selectByOne(String sno,String cno) {//查询赋值， 插入赋值更新，更新赋值插入

        try {
            connection=JdbcUtils.getConnection();

            //3.操作对象
            //一个人选了多门课，需要两个参数才能查询一门课的成绩
            String sql="  select student.sno,student.sname,student.ssex,student.speciality,course.cno, course.cname, score.grade " +
                    " from student,score,course where student.sno=score.sno and score.cno=course.cno  and score.sno=? and score.cno=?";//sql里面不允许有分号
            preparedStatement= connection.prepareStatement(sql);

            preparedStatement.setString(1,sno);//拼接参数？
            preparedStatement.setString(2,cno);
            resultSet=preparedStatement.executeQuery();//查询到的结果赋值给结果集对象

            //4.处理结果集
            ScoreVo scoreVo = new ScoreVo();
            while(resultSet.next()){

                //student.sno,student.sname,student.ssex,student.speciality,course.cno, course.cname, score.grade
                scoreVo.setSno(resultSet.getString("sno"));

                scoreVo.setSname(resultSet.getString("sname"));
                scoreVo.setSsex(resultSet.getString("ssex"));
                scoreVo.setSpeciality(resultSet.getString("speciality"));
                scoreVo.setCno(resultSet.getString("cno"));
                scoreVo.setCname(resultSet.getString("cname"));
                scoreVo.setGrade(resultSet.getFloat("grade"));

                System.out.println(scoreVo.getCname());
            }
            return scoreVo;//查询成功，返回集合

        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            //关闭资源
            JdbcUtils.closeResoure(connection,preparedStatement,resultSet);
        }

        return null;
    }
}
